/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.AccountDao;
import com.tesam.gedeon.entity.Account;
import com.tesam.gedeon.entity.Country;
import com.tesam.gedeon.entity.Department;
import com.tesam.gedeon.entity.District;
import com.tesam.gedeon.entity.Province;
import java.io.File;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.web.multipart.MultipartFile;

/**
 *
 * @author Sistema
 */
@Repository
public class AccountDaoImpl extends SimpleJdbcDaoSupport implements AccountDao {
    
    @Autowired
    public AccountDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    
}

    @Override
    public int getCountAll() {
        return getJdbcTemplate().queryForInt("select count(*) from account where deleted = 0");
    }

    @Override
    public int getCountBySearch(Account account) {
        return getJdbcTemplate().queryForInt("select count(*) as total "+
            " from account ac " +
            " left join country co on co.longcode = ac.country_bill " +
            " left join country co2 on co2.longcode = ac.country_shipping " +
            " left join department de on de.code = ac.department_bill and de.code_country = ac.country_bill " +
            " left join department de2 on de2.code = ac.department_shipping and de2.code_country = ac.country_shipping " +
            " left join province pr on pr.code = ac.province_bill and pr.code_department = ac.department_bill and pr.code_country = ac.country_bill " +
            " left join province pr2 on pr2.code = ac.province_shipping and pr2.code_department = ac.department_bill and pr2.code_country = ac.country_shipping " +
            " left join district di on di.code = ac.district_bill and di.code_province = ac.province_bill   and di.code_department = ac.department_bill and di.code_country = ac.country_bill " +
            " left join district di2 on di2.code = ac.district_shipping and di2.code_province = ac.province_shipping and di2.code_department = ac.department_shipping and di2.code_country = ac.country_shipping " +
            " where ac.deleted = 0 and upper(ac.name) like '%"+account.getName().toUpperCase()+"%' " );
    }

    @Override
    public List<Account> getListByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        List<Account> result = new ArrayList();
        try{
            String sql = "select ac.* , co.name as countrybill , co2.name as countryshipping , de.name as departmentbill ,de2.name as departmentshipping , pr.name as provincebill ,  " +
            " pr2.name  as provinceshipping , di.name as districtbill ,di.zipcode as zipcodebill , di2.name as districtshipping ,di2.zipcode as zipcodeshipping  " +
            " from account ac " +
            " left join country co on co.longcode = ac.country_bill " +
            " left join country co2 on co2.longcode = ac.country_shipping " +
            " left join department de on de.code = ac.department_bill and de.code_country = ac.country_bill " +
            " left join department de2 on de2.code = ac.department_shipping and de2.code_country = ac.country_shipping " +
            " left join province pr on pr.code = ac.province_bill and pr.code_department = ac.department_bill and pr.code_country = ac.country_bill " +
            " left join province pr2 on pr2.code = ac.province_shipping and pr2.code_department = ac.department_bill and pr2.code_country = ac.country_shipping " +
            " left join district di on di.code = ac.district_bill and di.code_province = ac.province_bill   and di.code_department = ac.department_bill and di.code_country = ac.country_bill " +
            " left join district di2 on di2.code = ac.district_shipping and di2.code_province = ac.province_shipping and di2.code_department = ac.department_shipping and di2.code_country = ac.country_shipping " +
            " where ac.deleted = 0 "+
            "order by "+dataToSort+" "+directionOfOrder+" limit "+
            endLimit+" offset "+startLimit;
            
            List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
                    Account account = new Account();
                    account.setId((Integer)(row.get("id")));
                    account.setName((String)(row.get("name")));
                    account.setPhone((String)(row.get("phone")));
                    account.setEmail((String)(row.get("email")));
                    account.setWebsite((String)(row.get("website")));
                    account.setFax((String)(row.get("fax")));
                    account.setImageName((String)(row.get("imagename")));
                    account.setFoundation((Date)(row.get("foundation")));
                    account.setAccountNo((String)(row.get("account_no"))); 
                    
                    Country country = new Country();
                    country.setLongCode((String)(row.get("country_bill")));
                    country.setName((String)(row.get("countryBill")));
                    account.setCountryBill(country);
                    
                    country = new Country();
                    country.setLongCode((String)(row.get("country_shipping")));
                    country.setName((String)(row.get("countryShipping")));
                    account.setCountryShipping(country);
                    
                    Department department = new Department();
                    department.setCode((String)(row.get("department_bill")));
                    department.setName((String)(row.get("departmentbill")));
                    account.setDepartmentBill(department);
                    
                    department = new Department();
                    department.setCode((String)(row.get("department_shipping")));
                    department.setName((String)(row.get("departmentShipping")));
                    account.setDepartmentShipping(department);
                    
                    Province province = new Province();
                    province.setCode((String)(row.get("province_bill")));
                    province.setName((String)(row.get("provincebill")));
                    account.setProvinceBill(province);
                    
                    province = new Province();
                    province.setCode((String)(row.get("province_shipping")));
                    province.setName((String)(row.get("provinceShipping")));
                    account.setProvinceShipping(province);
                    
                    District district = new District();
                    district.setCode((String)(row.get("district_bill")));
                    district.setName((String)(row.get("districtbill")));
                    account.setDistrictBill(district);
                    
                    district = new District();
                    district.setCode((String)(row.get("district_shipping")));
                    district.setName((String)(row.get("districtShipping")));
                    account.setDistrictShipping(district);
                    
                    account.setEmailBill((String)(row.get("email_bill")));
                    account.setEmailShipping((String)(row.get("email_shipping")));
                    account.setAddressBill((String)(row.get("address_bill")));
                    account.setAddressShipping((String)(row.get("address_shipping")));
                    
                    result.add(account);
                }
        }catch(Exception e){
            System.out.println("getListByLimit : "+e.getMessage());
        }
        return result ;
    }

    @Override
    public List<Account> getListBySearch(int startLimit, int endLimit, String dataToSort, String directionOfOrder, Account account) {
        List<Account> result = new ArrayList();
        try{
            String sql = "select ac.* , co.name as countrybill , co2.name as countryshipping , de.name as departmentbill ,de2.name as departmentshipping , pr.name as provincebill ,  " +
            " pr2.name  as provinceshipping , di.name as districtbill ,di.zipcode as zipcodebill , di2.name as districtshipping ,di2.zipcode as zipcodeshipping  " +
            " from account ac " +
            " left join country co on co.longcode = ac.country_bill " +
            " left join country co2 on co2.longcode = ac.country_shipping " +
            " left join department de on de.code = ac.department_bill and de.code_country = ac.country_bill " +
            " left join department de2 on de2.code = ac.department_shipping and de2.code_country = ac.country_shipping " +
            " left join province pr on pr.code = ac.province_bill and pr.code_department = ac.department_bill and pr.code_country = ac.country_bill " +
            " left join province pr2 on pr2.code = ac.province_shipping and pr2.code_department = ac.department_bill and pr2.code_country = ac.country_shipping " +
            " left join district di on di.code = ac.district_bill and di.code_province = ac.province_bill   and di.code_department = ac.department_bill and di.code_country = ac.country_bill " +
            " left join district di2 on di2.code = ac.district_shipping and di2.code_province = ac.province_shipping and di2.code_department = ac.department_shipping and di2.code_country = ac.country_shipping " +
            " where ac.deleted = 0 and upper(ac.name) like '%"+account.getName().toUpperCase()+"%' "+
            "order by "+dataToSort+" "+directionOfOrder+" limit "+
            endLimit+" offset "+startLimit;
            
            List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
                    Account accountE = new Account();
                    accountE.setId((Integer)(row.get("id")));
                    accountE.setName((String)(row.get("name")));
                    accountE.setPhone((String)(row.get("phone")));
                    accountE.setEmail((String)(row.get("email")));
                    accountE.setWebsite((String)(row.get("website")));
                    accountE.setFax((String)(row.get("fax")));
                    accountE.setImageName((String)(row.get("imagename")));
                    accountE.setFoundation((Date)(row.get("foundation")));
                    accountE.setAccountNo((String)(row.get("account_no"))); 
                    
                    Country country = new Country();
                    country.setLongCode((String)(row.get("country_bill")));
                    country.setName((String)(row.get("countryBill")));
                    accountE.setCountryBill(country);
                    
                    country = new Country();
                    country.setLongCode((String)(row.get("country_shipping")));
                    country.setName((String)(row.get("countryShipping")));
                    accountE.setCountryShipping(country);
                    
                    Department department = new Department();
                    department.setCode((String)(row.get("department_bill")));
                    department.setName((String)(row.get("departmentbill")));
                    accountE.setDepartmentBill(department);
                    
                    department = new Department();
                    department.setCode((String)(row.get("department_shipping")));
                    department.setName((String)(row.get("departmentShipping")));
                    accountE.setDepartmentBill(department);
                    
                    Province province = new Province();
                    province.setCode((String)(row.get("province_bill")));
                    province.setName((String)(row.get("provincebill")));
                    accountE.setProvinceBill(province);
                    
                    province = new Province();
                    province.setCode((String)(row.get("province_shipping")));
                    province.setName((String)(row.get("provinceShipping")));
                    accountE.setProvinceBill(province);
                    
                    District district = new District();
                    district.setCode((String)(row.get("district_bill")));
                    district.setName((String)(row.get("districtbill")));
                    accountE.setDistrictBill(district);
                    
                    district = new District();
                    district.setCode((String)(row.get("district_shipping")));
                    district.setName((String)(row.get("districtShipping")));
                    accountE.setDistrictBill(district);
                    
                    accountE.setEmailBill((String)(row.get("email_bill")));
                    accountE.setEmailShipping((String)(row.get("email_shipping")));
                    accountE.setAddressBill((String)(row.get("address_bill")));
                    accountE.setAddressShipping((String)(row.get("address_shipping")));
                    
                    result.add(accountE);
                }
        }catch(Exception e){
            System.out.println("getListByLimit : "+e.getMessage());
        }
        return result ;
    }

    @Override
    public List<Account> getListByOwner(Account account){
        List<Account> result = new ArrayList();
            
        try{ 
            String sql = "select ac.* , co.name as countrybill , co2.name as countryshipping , de.name as departmentbill ,de2.name as departmentshipping , pr.name as provincebill ,  " +
            " pr2.name  as provinceshipping , di.name as districtbill ,di.zipcode as zipcodebill , di2.name as districtshipping ,di2.zipcode as zipcodeshipping  " +
            " from account ac " +
            " left join country co on co.longcode = ac.country_bill " +
            " left join country co2 on co2.longcode = ac.country_shipping " +
            " left join department de on de.code = ac.department_bill and de.code_country = ac.country_bill " +
            " left join department de2 on de2.code = ac.department_shipping and de2.code_country = ac.country_shipping " +
            " left join province pr on pr.code = ac.province_bill and pr.code_department = ac.department_bill and pr.code_country = ac.country_bill " +
            " left join province pr2 on pr2.code = ac.province_shipping and pr2.code_department = ac.department_bill and pr2.code_country = ac.country_shipping " +
            " left join district di on di.code = ac.district_bill and di.code_province = ac.province_bill   and di.code_department = ac.department_bill and di.code_country = ac.country_bill " +
            " left join district di2 on di2.code = ac.district_shipping and di2.code_province = ac.province_shipping and di2.code_department = ac.department_shipping and di2.code_country = ac.country_shipping " +
            " where ac.deleted = 0 and ac.ownerid =  "+account.getOwnerId()+"  order by id desc limit 20 " ;
             
            List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
                    Account accountE = new Account();
                    accountE.setId((Integer)(row.get("id")));
                    accountE.setName((String)(row.get("name")));
                    accountE.setPhone((String)(row.get("phone")));
                    accountE.setEmail((String)(row.get("email")));
                    accountE.setWebsite((String)(row.get("website")));
                    accountE.setFax((String)(row.get("fax")));
                    accountE.setImageName("nopicture.jpg");
                    if(row.get("imagename") != null){
                        if(row.get("imagename") != ""){
                         accountE.setImageName((String)(row.get("imagename")));
                        }
                    }
                    
                    accountE.setFoundation((Date)(row.get("foundation")));
                    accountE.setAccountNo((String)(row.get("account_no"))); 
                    accountE.setCurrentAccount((String) (row.get("current_account")));
                    
                    Country country = new Country();
                    country.setLongCode((String)(row.get("country_bill")));
                    country.setName((String)(row.get("countryBill")));
                    accountE.setCountryBill(country);
                    
                    country = new Country();
                    country.setLongCode((String)(row.get("country_shipping")));
                    country.setName((String)(row.get("countryShipping")));
                    accountE.setCountryShipping(country);
                    
                    Department department = new Department();
                    department.setCode((String)(row.get("department_bill")));
                    department.setName((String)(row.get("departmentbill")));
                    accountE.setDepartmentBill(department);
                    
                    department = new Department();
                    department.setCode((String)(row.get("department_shipping")));
                    department.setName((String)(row.get("departmentShipping")));
                    accountE.setDepartmentShipping(department);
                    
                    Province province = new Province();
                    province.setCode((String)(row.get("province_bill")));
                    province.setName((String)(row.get("provincebill")));
                    accountE.setProvinceBill(province);
                    
                    province = new Province();
                    province.setCode((String)(row.get("province_shipping")));
                    province.setName((String)(row.get("provinceshipping")));
                    accountE.setProvinceShipping(province);
                    
                    District district = new District();
                    district.setCode((String)(row.get("district_bill")));
                    district.setName((String)(row.get("districtbill")));
                    accountE.setDistrictBill(district);
                    
                    district = new District();
                    district.setCode((String)(row.get("district_shipping")));
                    district.setName((String)(row.get("districtShipping")));
                    accountE.setDistrictShipping(district);
                    
                    accountE.setEmailBill((String)(row.get("email_bill")));
                    accountE.setEmailShipping((String)(row.get("email_shipping")));
                    accountE.setAddressBill((String)(row.get("address_bill")));
                    accountE.setAddressShipping((String)(row.get("address_shipping")));
                    
                  result.add(accountE);
                }
        }catch(Exception e){
            System.out.println("getListByLimit : "+e.getMessage());
        }
        
        return result ;
    }
    
    @Override
    public List<Account> getAccountById(Account account) {
        List<Account> result = new ArrayList();
         
        try{
            getJdbcTemplate().update("update account set viewedtime = NOW() where id = "+account.getId());
            
            String sql = "select ac.* , co.name as countrybill , co2.name as countryshipping , de.name as departmentbill ,de2.name as departmentshipping , pr.name as provincebill ,  " +
            " pr2.name  as provinceshipping , di.name as districtbill ,di.zipcode as zipcodebill , di2.name as districtshipping ,di2.zipcode as zipcodeshipping  " +
            " from account ac " +
            " left join country co on co.longcode = ac.country_bill " +
            " left join country co2 on co2.longcode = ac.country_shipping " +
            " left join department de on de.code = ac.department_bill and de.code_country = ac.country_bill " +
            " left join department de2 on de2.code = ac.department_shipping and de2.code_country = ac.country_shipping " +
            " left join province pr on pr.code = ac.province_bill and pr.code_department = ac.department_bill and pr.code_country = ac.country_bill " +
            " left join province pr2 on pr2.code = ac.province_shipping and pr2.code_department = ac.department_bill and pr2.code_country = ac.country_shipping " +
            " left join district di on di.code = ac.district_bill and di.code_province = ac.province_bill   and di.code_department = ac.department_bill and di.code_country = ac.country_bill " +
            " left join district di2 on di2.code = ac.district_shipping and di2.code_province = ac.province_shipping and di2.code_department = ac.department_shipping and di2.code_country = ac.country_shipping " +
            " where ac.deleted = 0 and ac.id =  "+account.getId() ;
             
            List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
                    Account accountE = new Account();
                    accountE.setId((Integer)(row.get("id")));
                    accountE.setName((String)(row.get("name")));
                    accountE.setPhone((String)(row.get("phone")));
                    accountE.setEmail((String)(row.get("email")));
                    accountE.setWebsite((String)(row.get("website")));
                    accountE.setFax((String)(row.get("fax")));
                    accountE.setImageName("nopicture.jpg");
                    if(row.get("imagename") != null){
                        if(row.get("imagename") != ""){
                         accountE.setImageName((String)(row.get("imagename")));
                        }
                    }
                    
                    accountE.setFoundation((Date)(row.get("foundation")));
                    accountE.setAccountNo((String)(row.get("account_no"))); 
                    accountE.setCurrentAccount((String) (row.get("current_account")));
                    
                    Country country = new Country();
                    country.setLongCode((String)(row.get("country_bill")));
                    country.setName((String)(row.get("countryBill")));
                    accountE.setCountryBill(country);
                    
                    country = new Country();
                    country.setLongCode((String)(row.get("country_shipping")));
                    country.setName((String)(row.get("countryShipping")));
                    accountE.setCountryShipping(country);
                    
                    Department department = new Department();
                    department.setCode((String)(row.get("department_bill")));
                    department.setName((String)(row.get("departmentbill")));
                    accountE.setDepartmentBill(department);
                    
                    department = new Department();
                    department.setCode((String)(row.get("department_shipping")));
                    department.setName((String)(row.get("departmentShipping")));
                    accountE.setDepartmentShipping(department);
                    
                    Province province = new Province();
                    province.setCode((String)(row.get("province_bill")));
                    province.setName((String)(row.get("provincebill")));
                    accountE.setProvinceBill(province);
                    
                    province = new Province();
                    province.setCode((String)(row.get("province_shipping")));
                    province.setName((String)(row.get("provinceshipping")));
                    accountE.setProvinceShipping(province);
                    
                    District district = new District();
                    district.setCode((String)(row.get("district_bill")));
                    district.setName((String)(row.get("districtbill")));
                    accountE.setDistrictBill(district);
                    
                    district = new District();
                    district.setCode((String)(row.get("district_shipping")));
                    district.setName((String)(row.get("districtShipping")));
                    accountE.setDistrictShipping(district);
                    
                    accountE.setEmailBill((String)(row.get("email_bill")));
                    accountE.setEmailShipping((String)(row.get("email_shipping")));
                    accountE.setAddressBill((String)(row.get("address_bill")));
                    accountE.setAddressShipping((String)(row.get("address_shipping")));
                    
                  result.add(accountE);
                }
        }catch(Exception e){
            System.out.println("getListByLimit : "+e.getMessage());
        }
        return result ;
    }

    @Override
    public int save(Account account) {
        int id = 0 ;
            try{
                account = this.copyPicture(account);
                 
                getJdbcTemplate().update("insert into account "
                 + "(createdby , datecreated , status , deleted , presence , "+ 
                 "ownerid , name , phone , "
               + "email , website ,fax , "
               + "imagename ,  country_bill , department_bill ," +
                 " province_bill , district_bill , email_bill , "
               + "address_bill , zipcode_bill ,country_shipping ," +
                " department_shipping , province_shipping , district_shipping , "
              + "email_shipping , address_shipping , zipcode_shipping ," + 
                " account_no , current_account , foundation ) values("+
                "? , NOW() , 1 , 0, 0 , "+
                "? , ? , ? , "
              + "? , ? , ? , "
              + "? , ? , ? , "
              + "? , ? , ? , "
              + "? , ? , ? , "
              + "? , ? , ? , "
              + "? , ? , ? , "+
                "? , ? , ?  )" , 
            account.getCreatedBy() , 
            account.getOwnerId() ,account.getName() , account.getPhone() , 
            account.getEmail() ,account.getWebsite() , account.getFax() , 
            account.getImageName()   , account.getCountryBill().getLongCode(), account.getDepartmentBill().getCode() ,
            account.getProvinceBill().getCode(),account.getDistrictBill().getCode() ,account.getEmailBill(),
            account.getAddressBill(), account.getZipcodeBill(),account.getCountryShipping().getLongCode() ,
            account.getDepartmentShipping().getCode(),account.getProvinceShipping().getCode(),account.getDistrictShipping().getCode(),
            account.getEmailShipping(),account.getAddressShipping(),account.getZipcodeShipping(),
            account.getAccountNo() , account.getCurrentAccount() , account.getFoundation());
                
                id = getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
                 
                
            }catch(Exception e){
            System.out.println("save : "+e.getMessage());
            }
        return id ;
    }

    private Account copyPicture(Account account){
        try {
            MultipartFile multipart = account.getImageFile();
            
            String pathFolder = "C:\\Users\\Sistema\\Desktop\\GedeonMvc\\trunk\\GedeonMvc\\web\\resources\\accountPictures\\";
            String nombre = "" ;
            boolean exist = true ;
            int turn = 1 ;
            File f ;
            while(exist == true){
                multipart.getContentType() ;
                nombre = turn+"-"+multipart.getOriginalFilename();
                
                System.out.println("path folder "+turn+"° : "+pathFolder+nombre);
                 f = new File(pathFolder+nombre);
                exist = f.exists() ;
                turn++;
            }
            
             File path = new File(pathFolder);
             multipart.transferTo(new File(path, nombre));
             account.setImageName(nombre);
            } catch (Exception e) {
             account.setImageName("nopicture.jpg");
             System.out.println("no se pudo copiar : "+e.getMessage()); 
            }
        
        return account ;
    }
    
    @Override
    public void update(Account account) {
        
       account =  this.copyPicture(account);
        
            String sql = "update account set name = '"+account.getName()+"' , phone = '"+account.getPhone()+"' ,"
                    + "email = '"+account.getEmail()+"' , website = '"+account.getWebsite()+"' , "
                    + "foundation = '"+account.getFoundation()+"' ,"
                    + "fax = '"+account.getFax()+"' , country_bill = '"+account.getCountryBill().getLongCode()+"' , "
                    +"department_bill = '"+account.getDepartmentBill().getCode()+"' , "
                    + "province_bill = '"+account.getProvinceBill().getCode()+"' , "
                    + "district_bill = '"+account.getDistrictBill().getCode()+"' , "
                    + "email_bill = '"+account.getEmailBill()+"' , "
                    + "address_bill = '"+account.getAddressBill()+"' , "
                    + "zipcode_bill = '"+account.getZipcodeBill()+"' , "
                    + "country_shipping = '"+account.getCountryShipping().getLongCode()+"' ,"
                    + "department_shipping = '"+account.getDepartmentShipping().getCode()+"' ,"
                    + "province_shipping = '"+account.getProvinceShipping().getCode()+"' , "
                    + "district_shipping = '"+account.getDistrictShipping().getCode()+"' ,"
                    + "email_shipping = '"+account.getEmailShipping()+"' , "
                    + "address_shipping = '"+account.getAddressShipping()+"' , "
                    + "zipcode_shipping = '"+account.getZipcodeShipping()+"' , "
                    + "account_no = '"+account.getAccountNo()+"' , "
                    + "current_account = '"+account.getCurrentAccount()+"' , "
                    + "imagename = '"+account.getImageName()+"' , "
                    + "datemodified = NOW() "
                    + "where id = "+account.getId();
            System.out.println(sql);
            getJdbcTemplate().update(sql);
    }

    @Override
    public void delete(Account account) {
        try{
            getJdbcTemplate().update("update account set deleted = 1 where id = "+account.getId());
        }catch(Exception e){
            System.out.println("delete : "+e.getMessage());
        }
    }
    
}
